/*

This code cleans ASM Fuel Trailers -- 1976, 1977 (CMF), 1978, 1979, 1980, 1981

*/


*>------------------ loop over and read in all files --------------------------<
local i = 0
foreach y of numlist 1976 1977 1978 1979 1980 1981 {
	
	* merge with ASM/CMF file from this year to get electricity, industry code, and lbdnum info 
	import sas using "/data/economic/lbd/`y'/lbd`y'c_c201600.sas7bdat", case(lower) clear
	drop if mi(lbdnum) | mi(firstyear)
	replace yr = `y'
	tempfile lbd`y'
	sa `lbd`y'', replace
	
	if `y' != 1977 {
		import sas using "/data/economic/asm/`y'/asm`y'.sas7bdat", case(lower) clear
		duplicates drop lbdnum, force
		merge 1:1 lbdnum using `lbd`y'', keepusing(bestsic bestnaics) assert(1 2 3) keep(3) nogen
		keep bestsic ppn cf fipsst lbdnum ee pe bestnaics
		gen bestsic4 = substr(bestsic,1,4)
		destring bestsic4, replace
		drop bestsic
		tempfile vars_`y'
		sa `vars_`y'', replace
		
		import sas using "/data/economic/asm/`y'/asm`y'fuel.sas7bdat", case(lower) clear
		merge 1:1 ppn using `vars_`y'', assert(1 2 3) keep(3) nogen
		drop ppn orgppn
	}
	else {
		import sas using "/data/economic/cmf/`y'/cmf`y'.sas7bdat", case(lower) clear
		duplicates drop lbdnum, force
		merge 1:1 lbdnum using `lbd`y'', keepusing(bestsic bestnaics) assert(1 2 3) keep(3) nogen
		keep ppn cf fipsst lbdnum ee pe bestsic bestnaics
		gen bestsic4 = substr(bestsic,1,4)
		destring bestsic4, replace
		drop bestsic
		tempfile vars_`y'
		sa `vars_`y'', replace
		
		import sas using "/data/economic/cmf/`y'/cmf`y'fuel.sas7bdat", case(lower) clear
		merge 1:1 ppn using `vars_`y'', assert(1 2 3) keep(3) nogen
		drop ppn orgppn
	}
	
	
	* concord industry codes to 1997 NAICS
	* first concord to 1987 NAICS
	preserve
		import delimited "/projects/dstafftransfer/transfer.20200428/conc_sic72_sic87.csv", clear
		duplicates drop sic72, force
		tempfile concord72`y'
		sa `concord72`y'', replace
	restore
	ren bestsic4 sic72
	merge m:1 sic72 using `concord72`y'', assert(1 2 3) keep(1 3) keepusing(sic72 sic87) nogen
	ren sic72 bestsic4
	replace bestsic4 = sic87 if !mi(sic87)
	drop sic87
	* second concord 1987 NAICS to 1997 NAICS
	preserve
		import delimited "/projects/dstafftransfer/transfer.20200428/conc_sic87_naics97.csv", clear
		duplicates drop sic, force
		ren sic bestsic4
		tempfile concord87`y'
		sa `concord87`y'', replace
	restore
	merge m:1 bestsic4 using `concord87`y'', assert(1 2 3) keep(1 3) keepusing(bestsic4 naics97) nogen
	la var bestsic4 "SIC 1987 Rev."
	
	replace bestnaics = "" if mi(bestnaics)
	
	gen last_naics = substr(bestnaics,8,1)
	capture confirm numeric variable last_naics
	gen byte notnumeric = real(last_naics) == .
	replace bestnaics = substr(bestnaics,1,7) + "0" if notnumeric == 1
	drop last_naics notnumeric
	
	replace bestnaics = substr(bestnaics,1,6)
	destring bestnaics, replace
	replace bestnaics = naics97 if !mi(naics97) 
	drop naics97
	la var bestnaics "NAICS Rev. 1997 Ind. Code"
		
	if `i' == 1 append using "/projects/data/dataSTATA/economic/asmFuels_merged.dta"
	local i = 1
	sa "/projects/data/dataSTATA/economic/asmFuels_merged.dta", replace	
}

* deflate
import delimited "/projects/dstafftransfer/transfer.20200428/naics5811.csv", clear 
* relevant deflators:
foreach var of varlist piship pimat pien piinv {
	gen `var'_n = `var' if year == 2011
	bys naics: egen `var'_2011 = mean(`var'_n)
	gen `var'_dfltr = `var'_2011 / `var'
	drop `var'_n `var'_2011 
	
}
keep naics year pien_dfltr
ren naics bestnaics

tempfile deflators
sa `deflators', replace

use "/projects/data/dataSTATA/economic/asmFuels_merged.dta", clear
merge m:1 bestnaics year using `deflators', assert(1 2 3) keep(1 3) nogen
ren ee c_elec
ren pe q_elec
foreach var of varlist c_* {
	replace `var' = `var' * pien_dfltr 
}
replace cf = cf * pien_dfltr
drop lbdrevlnk lbdnum_c201100 lbdnum_c201101 lbdnum_c201200 lbdnum_c201300 lbdnum_c201400 lbdnum_c201500 lbdnum_c201600
destring fipsst, replace


foreach var in coal ngas coke lpg roil doil { 
	replace c_`var' = . if mi(q_`var') | q_`var' == 0
	replace q_`var' = . if mi(c_`var') | c_`var' == 0
}

** Units conversions for fuel variables and from electricity to co2 per state
** Performed in secondary script for disclosure purposes

do projects/programs/codeSTATA/A_cleaning/3_cleanfueltrailers_unitsconversions.do

drop q_* cf

* industry-average BTU per $ raw fuel expenditures
	egen btu_tot = rowtotal( *_btu)
	la var btu_tot "Total Fuel Consumed (million BTU), incl. Electricity"
	egen cf_tot = rowtotal( *_e)
	la var cf_tot "Total Fuels Expenditures (1000 2011 USD), incl. Electricity"

	gen btu_cf = (btu_tot / ( cf_tot  * 1000) )
	la var btu_cf "Energy Fuel Intensity (million BTU per USD Fuel Expenditure, 2011)"
	egen co2_tot = rowtotal( *_co2)
	la var co2_tot "Total CO2 Consumed (kg)"
	gen co2_cf = (co2_tot / (cf_tot * 1000) )
	la var co2_cf "CO2 Fuel Intensity (kg per USD Fuel Expenditure, 2011)"
	gen elec_btu_cf = elec_btu / (elec_e * 1000)
	la var elec_btu_cf "Electricity Fuel Intensity (million BTU per USD Electricity Expenditure, 2011)"
	gen elec_co2_cf = elec_co2 / (elec_e * 1000)
	la var elec_co2_cf "CO2 Electricity Intensity (kg per USD Electricity Expenditure, 2011)"
	
	gen btu_raw = btu_tot - elec_btu
	la var btu_raw "Total Raw Fuel Consumed (million BTU), excl. Electricity"
	gen co2_raw = co2_tot - elec_co2
	la var co2_raw "Total Raw Fuel Consumed (kg CO2), excl. Electricity"
	gen cf_raw  = cf_tot  - elec_e
	la var cf_raw "Total Raw Fuels Expenditures (1000 2011 USD, excl. Electricity)"
	
	gen btu_cf_raw = btu_raw / (cf_raw * 1000)
	la var btu_cf_raw "Energy Raw Fuel Intensity (million BTU per USD Fuel Expenditure, 2011), excl. Elec."
	gen co2_cf_raw = co2_raw / (cf_raw * 1000)
	la var co2_cf_raw "CO2 Raw Fuel Intensity (kg per USD Fuel Expenditure, 2011), excl. Elec."
	

sum btu_cf, detail
drop if btu_cf < `r(p1)'
sum btu_cf, detail
drop if btu_cf > `r(p99)'


sa "/projects/data/dataSTATA/economic/asmFuels_merged.dta", replace

collapse (mean) co2_cf_raw  btu_cf_raw , by(bestnaics year)


la var btu_cf_raw "Industry-Avg Energy Raw Fuel Intensity (million BTU per USD Fuel Expenditure, 2011), excl. Elec."
la var co2_cf_raw "Industry-Avg CO2 Raw Fuel Intensity (kg per USD Fuel Expenditure, 2011), excl. Elec."

sa "/projects/data/dataSTATA/economic/asmFuels_merged_collapsed.dta", replace
